Create Custom Column for Seasons -Transforming Data
10
Creating Seasons column and Fatal-Nonfatal column:
Step 1.
Click on Transform Data under the home tab, In the Power Query Editor Click on IncidentDate column, then right click on the column and select duplicate column.
Step 2. Rename the duplicate column as Month No and press enter. Right click on the Month No column select transform then select Month to extract Month Number.
Follow the Gif below.
Step 3. Creating Custom Column for Seasons using Month No Column. Follow the Gif and Screen Shots.
= if [#".Month No"] =12 or [#".Month No"]=1 or [#".Month No"] =2 then "Winter"
else if [#".Month No"] =3 or [#".Month No"] =4 or [#".Month No"] =5 then "Spring"
else if [#".Month No"]=6 or [#".Month No"]=7or [#".Month No"]=8 then "Summer"
else if [#".Month No"]=9 or [#".Month No"]=10 or [#".Month No"]=11 then "Autumn"
else "NA"
after you write the formula click on ok.
You can get the Season Column like below. Click on the Season Column, Click on Transform tab and Click on Data Type and select Text to change the data type.
Step 4: To create Fatal-Nonfatal column
a. In the power query editor select Fatal(Y/N) column, right click on it - select
duplicate column then rename the duplicate column as Fatal-Nonfatal.
b. Right click on the column, select replace values. In the replace values window
provide value to find Y, replace with Fatal, do the same with value to find for N,
replace with NonFatal, and value to find null, replace with Unknown. Then click on
close and apply. Follow the gif below.
Conditional Column in Power BI using Power Query:
https://radacad.com/conditional-column-in-power-bi-using-power-query-you-can-do-anything
Create Custom Column for Seasons -Transforming Data
10
Creating Seasons column and Fatal-Nonfatal column:
Step 1.
Click on Transform Data under the home tab, In the Power Query Editor Click on IncidentDate column, then right click on the column and select duplicate column.
Step 2. Rename the duplicate column as Month No and press enter. Right click on the Month No column select transform then select Month to extract Month Number.
Follow the Gif below.
Step 3. Creating Custom Column for Seasons using Month No Column. Follow the Gif and Screen Shots.
= if [#".Month No"] =12 or [#".Month No"]=1 or [#".Month No"] =2 then "Winter"
else if [#".Month No"] =3 or [#".Month No"] =4 or [#".Month No"] =5 then "Spring"
else if [#".Month No"]=6 or [#".Month No"]=7or [#".Month No"]=8 then "Summer"
else if [#".Month No"]=9 or [#".Month No"]=10 or [#".Month No"]=11 then "Autumn"
else "NA"
after you write the formula click on ok.
You can get the Season Column like below. Click on the Season Column, Click on Transform tab and Click on Data Type and select Text to change the data type.
Step 4: To create Fatal-Nonfatal column
a. In the power query editor select Fatal(Y/N) column, right click on it - select
duplicate column then rename the duplicate column as Fatal-Nonfatal.
b. Right click on the column, select replace values. In the replace values window
provide value to find Y, replace with Fatal, do the same with value to find for N,
replace with NonFatal, and value to find null, replace with Unknown. Then click on
close and apply. Follow the gif below.
Conditional Column in Power BI using Power Query:
https://radacad.com/conditional-column-in-power-bi-using-power-query-you-can-do-anything